﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Text;
using System.IO;
using System.Web.UI;
using System.Web.UI.WebControls;

namespace QualityRPT.Common
{
    public static class Common
    {
        public static void ExportData(HttpResponse response, DataTable dt, SpreadsheetGear.IWorkbook workbook, int sheetIndex, string FileName, string checkDate, string[] aa)
        {
            SpreadsheetGear.IWorksheet worksheet = workbook.Worksheets[sheetIndex];
            SpreadsheetGear.IRange cells = worksheet.Cells;

            SpreadsheetGear.IRange cellStart = cells["B:B"];
            SpreadsheetGear.IRange rowStart = cells["5:5"];
            for (int i = 0; i < dt.Columns.Count - 1; i++)
                cellStart.Cells.Insert(SpreadsheetGear.InsertShiftDirection.Right);
            for (int i = 0; i < dt.Columns.Count; i++)
                cells.Rows[2, i + 1].Value = dt.Columns[i].ColumnName;
            for (int i = 0; i < dt.Rows.Count - 2; i++)
                rowStart.Rows.Insert(SpreadsheetGear.InsertShiftDirection.Down);

            for (int i = 0; i < dt.Rows.Count; i++)
            {
                cells.Rows[i + 3, 0].Value = i + 1;
                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    if (dt.Rows[i][j].ToString() != null && dt.Rows[i][j].ToString() != "")
                    {
                        cells.Rows[i + 3, j + 1].Value = dt.Rows[i][j].ToString();
                    }
                    else
                    {
                        cells.Rows[i + 3, j + 1].Value = 0;
                    }        
                }
            }
            for (int i = 0; i < aa.Length; i++)
            {
                cells[aa[i] + ":" + aa[i]].NumberFormat = "0.00%";
            }
            cells.Rows[0, 0, 0, dt.Columns.Count - 1].Merge();
            cells.Rows[1, 1].Value = checkDate;
            cells.Rows[1, 1].HorizontalAlignment = SpreadsheetGear.HAlign.Left;
            cells.Rows[dt.Rows.Count + 3, dt.Columns.Count - 2].Value = "打印时间：";
            cells.Rows[dt.Rows.Count + 3, dt.Columns.Count - 1].Value = DateTime.Now.ToString();
            if (dt.Rows.Count < 2)
                cells.Rows["5:5"].Delete();

            //设置行高
            cells.Rows.RowHeight = 20;
            cells.Rows[0, 0].RowHeight = 30;
            cells.Rows[2, 0].Value = "序号";

            response.Clear();
            response.ContentType = "application/octet-stream";
            //使用UTF-8对文件名进行编码
            response.AppendHeader("Content-Disposition", "attachment;filename=\"" + HttpUtility.UrlEncode(FileName, System.Text.Encoding.UTF8) + "\"");

            response.ContentType = "application/vnd.ms-excel";
            response.AddHeader("Content-Disposition", "attachment;filename=" + FileName);
            workbook.SaveToStream(response.OutputStream, SpreadsheetGear.FileFormat.Excel8);
            response.End();
        }

        public static void NOAutoExportData(HttpResponse response, DataTable dt, SpreadsheetGear.IWorkbook workbook, int sheetIndex, string FileName, string checkDate, string[] aa)
        {
            SpreadsheetGear.IWorksheet worksheet = workbook.Worksheets[sheetIndex];
            SpreadsheetGear.IRange cells = worksheet.Cells;

            SpreadsheetGear.IRange cellStart = cells["B:B"];
            SpreadsheetGear.IRange rowStart = cells["5:5"];
            //自动向右复制列格式
            //for (int i = 0; i < dt.Columns.Count - 1; i++)
            //    cellStart.Cells.Insert(SpreadsheetGear.InsertShiftDirection.Right);
            for (int i = 0; i < dt.Columns.Count; i++)
                cells.Rows[2, i + 1].Value = dt.Columns[i].ColumnName;
            for (int i = 0; i < dt.Rows.Count - 2; i++)
                rowStart.Rows.Insert(SpreadsheetGear.InsertShiftDirection.Down);

            for (int i = 0; i < dt.Rows.Count; i++)
            {
                cells.Rows[i + 3, 0].Value = i + 1;
                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    if (dt.Rows[i][j].ToString() != null && dt.Rows[i][j].ToString() != "")
                    {               
                        cells.Rows[i + 3, j + 1].Value = dt.Rows[i][j].ToString();
                    }
                    else
                    {
                        cells.Rows[i + 3, j + 1].Value = 0;
                    }                                  
                }
            }
            for (int i = 0; i < aa.Length; i++)
            {
                cells[aa[i] + ":" + aa[i]].NumberFormat = "0.00%";
            }
            cells.Rows[0, 0, 0, dt.Columns.Count].Merge();
            cells.Rows[1, 1].Value = checkDate;
            cells.Rows[1, 1].HorizontalAlignment = SpreadsheetGear.HAlign.Left;
            cells.Rows[dt.Rows.Count + 3, dt.Columns.Count - 2].Value = "打印时间：";
            cells.Rows[dt.Rows.Count + 3, dt.Columns.Count - 1].Value = DateTime.Now.ToString();
            if (dt.Rows.Count < 2)
                cells.Rows["5:5"].Delete();

            //设置行高
            cells.Rows.RowHeight = 20;
            cells.Rows[0, 0].RowHeight = 30;
            cells.Rows[2, 0].Value = "序号";

            response.Clear();
            response.ContentType = "application/octet-stream";
            //使用UTF-8对文件名进行编码
            response.AppendHeader("Content-Disposition", "attachment;filename=\"" + HttpUtility.UrlEncode(FileName, System.Text.Encoding.UTF8) + "\"");

            response.ContentType = "application/vnd.ms-excel";
            response.AddHeader("Content-Disposition", "attachment;filename=" + FileName);
            workbook.SaveToStream(response.OutputStream, SpreadsheetGear.FileFormat.Excel8);
            response.End();
        }

        public static void ServiceExportData(HttpResponse response, DataTable dt, SpreadsheetGear.IWorkbook workbook, int sheetIndex, string FileName, string checkDate, string headname)
        {
            SpreadsheetGear.IWorksheet worksheet = workbook.Worksheets[sheetIndex];
            SpreadsheetGear.IRange cells = worksheet.Cells;

            SpreadsheetGear.IRange cellStart = cells["B:B"];
            SpreadsheetGear.IRange rowStart = cells["5:5"];
            for (int i = 0; i < dt.Columns.Count; i++)
                cells.Rows[2, i + 1].Value = dt.Columns[i].ColumnName;
            for (int i = 0; i < dt.Rows.Count - 2; i++)
                rowStart.Rows.Insert(SpreadsheetGear.InsertShiftDirection.Down);

            for (int i = 0; i < dt.Rows.Count; i++)
            {
                cells.Rows[i + 3, 0].Value = i + 1;
                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    if (dt.Rows[i][j].ToString() != null && dt.Rows[i][j].ToString() != "")
                    {
                        cells.Rows[i + 3, j + 1].Value = dt.Rows[i][j].ToString();
                    }
                    else
                    {
                        cells.Rows[i + 3, j + 1].Value = 0;
                    }
                }
            }
            cells.Rows[0, 0, 0, dt.Columns.Count].Merge();//合并
            cells.Rows[0, 0, 0, 0].Value = headname;
            cells.Rows[1, 1].Value = checkDate;
            cells.Rows[1, 1].HorizontalAlignment = SpreadsheetGear.HAlign.Left;
            cells.Rows[dt.Rows.Count + 3, dt.Columns.Count - 2].Value = "打印时间：";
            cells.Rows[dt.Rows.Count + 3, dt.Columns.Count - 1].Value = DateTime.Now.ToString();
            if (dt.Rows.Count < 2)
                cells.Rows["5:5"].Delete();

            //设置行高
            cells.Rows.RowHeight = 20;
            cells.Rows[0, 0].RowHeight = 30;
            cells.Rows[2, 0].Value = "NUM";

            response.Clear();
            response.ContentType = "application/octet-stream";
            //使用UTF-8对文件名进行编码
            response.AppendHeader("Content-Disposition", "attachment;filename=\"" + HttpUtility.UrlEncode(FileName, System.Text.Encoding.UTF8) + "\"");

            response.ContentType = "application/vnd.ms-excel";
            response.AddHeader("Content-Disposition", "attachment;filename=" + FileName);
            workbook.SaveToStream(response.OutputStream, SpreadsheetGear.FileFormat.Excel8);
            response.End();
        }             
    }
}